package com.hmj.excel.biz;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelPrase {
    private static final String EXCEL_XLS = "xls";//Excel 2003
    private static final String EXCEL_XLSX = "xlsx";// Excel 2007/2010
    private static Map<Integer,String> indexMap;
    static {
        init();
    }
    private static void init(){
        indexMap = new HashMap<Integer, String>();
        for (int i=0;i<26;i++){
            indexMap.put(i,String.valueOf((char)('A'+i)));
        }
    }

    /**
     * 读取excel文件
     * @param file
     * @param title 文件标题列
     * @return 返回数据对象
     * @throws IOException
     */
    public static List<JSONObject> readExcelFile(File file,Map title) throws IOException {
        Workbook wb = null;
        InputStream in = new FileInputStream(file);
        if(file.getName().endsWith(EXCEL_XLS)){
            wb = new HSSFWorkbook(in);
        }else if(file.getName().endsWith(EXCEL_XLSX)){
            wb = new XSSFWorkbook(in);
        }else{
            throw new RuntimeException("File format not support");
        }
        Sheet sheet = wb.getSheetAt(0);
        int count=0;
        List<JSONObject> dataList = new ArrayList<JSONObject>();
        for (Row row : sheet){
            short first = row.getFirstCellNum();
            short last = row.getLastCellNum();
            if(count==0){//第一行目录
                for(int k=first;k<last;k++){
                    Cell cell = row.getCell(k);
                    title.put(k,getCellValue(cell));
                }
            }else{
                JSONObject json = new JSONObject();
                for(int k=first;k<last;k++){
                    Cell cell = row.getCell(k);
                    json.put(indexMap.get(k),getCellValue(cell));
                }
                dataList.add(json);
            }
            count++;
        }
        wb.close();
        in.close();
        return dataList;
    }

    private static String getCellValue(Cell cell){
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int cellType = cell.getCellType();
        String cellValue = "";
        switch (cellType) {
            case Cell.CELL_TYPE_STRING:     // 文本
                cellValue = cell.getRichStringCellValue().getString();
                break;
            case Cell.CELL_TYPE_NUMERIC:    // 数字、日期
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellValue = fmt.format(cell.getDateCellValue());
                } else {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:    // 布尔型
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_BLANK: // 空白
                cellValue = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_ERROR: // 错误
                cellValue = "错误#";
                break;
            case Cell.CELL_TYPE_FORMULA:    // 公式
                // 得到对应单元格的公式
                //cellValue = cell.getCellFormula() + "#";
                // 得到对应单元格的字符串
                cell.setCellType(Cell.CELL_TYPE_STRING);
                cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                break;
            default:
                cellValue = "";
        }
        return cellValue;
    }

    /**
     * 导出excel文件
     * @param out
     * @param dataList
     * @param title
     */
    public static void exportExcelFile(OutputStream out,List<JSONObject> dataList,Map title) throws IOException {
        //创建Excel文档对象
        HSSFWorkbook workBook = new HSSFWorkbook();
        //创建文档空间sheet
        HSSFSheet sheet = workBook.createSheet("title_");
        //设置列标题
        HSSFRow row = sheet.createRow(0);
        for (int i=0;i<title.size();i++){
            row.createCell(i).setCellValue(title.get(i).toString());
        }
        //保存对象列表到文档
        for (int j=0;j<dataList.size();j++){
            JSONObject jsonObj = dataList.get(j);
            HSSFRow rowData = sheet.createRow(j+1);
            for (int k=0;k<title.size();k++){
//                if("total".equals(title.get(k))){
//                    rowData.createCell(k).setCellValue(jsonObj.getString("total"));
//                }else if("tax".equals(title.get(k))){
//                    rowData.createCell(k).setCellValue(jsonObj.getString("tax"));
//                }

                if(!jsonObj.containsKey(indexMap.get(k))){
                    if(jsonObj.containsKey(title.get(k))){
                        rowData.createCell(k).setCellValue(jsonObj.getString(title.get(k).toString()));
                    }else{
                        rowData.createCell(k).setCellValue("");
                    }
                }else{
                    rowData.createCell(k).setCellValue(jsonObj.getString(indexMap.get(k)));
                }

            }
        }

        workBook.write(out);
        workBook.close();
    }
    //排序列
    private static void orderBy(final String column, List<JSONObject> dataList){
        Collections.sort(dataList,new Comparator<JSONObject>(){
            public int compare(JSONObject arg0, JSONObject arg1) {
                return arg0.getString(column).compareTo(arg0.getString(column));
            }
        });
    }
}
